"""
飞检数据STEP8筛选 - conclude模块
============================================================================

将VLT表中的信息按照每个hsp_abbr、每个rule_id归纳到VLT2表中。

主要功能：
1. 检查并删除已存在的VLT2表
2. 创建VLT2表结构
3. 按照hsp_abbr和rule_id分组归纳数据
    3.1. 生成临时表 dxs, txs, vlt2_t0 用于辅助归纳
    3.2. 遍历每个hsp_abbr和rule_id组合，执行归纳插入操作

数据库连接与hspList参考 filte.py
============================================================================
"""

import json
import pandas as pd
pd.options.mode.chained_assignment = None  # 关闭警告
from sqlalchemy import create_engine, text
import os
import sys
import time
from datetime import datetime
from threading import Lock

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine

# 创建数据库引擎
engine = create_db_engine()

# 线程锁，用于同步进度显示
progress_lock = Lock()

def print_inline(msg: str):
    """单行滚动显示进度信息"""
    try:
        with progress_lock:
            # 使用 \r 回到行首，清除当前行内容并打印新内容
            sys.stdout.write('\r' + ' ' * 120)  # 先清除当前行
            sys.stdout.write('\r' + msg[:120])  # 打印新内容，限制长度
            sys.stdout.flush()
    except Exception:
        # 回退为普通打印
        print(msg)

def print_newline(msg: str = ""):
    """输出新行信息"""
    with progress_lock:
        sys.stdout.write('\n' + msg + '\n')
        sys.stdout.flush()

def format_time(seconds):
    """格式化时间显示"""
    if seconds < 60:
        return f"{seconds:.1f}秒"
    elif seconds < 3600:
        minutes = int(seconds // 60)
        secs = int(seconds % 60)
        return f"{minutes}分{secs}秒"
    else:
        hours = int(seconds // 3600)
        minutes = int((seconds % 3600) // 60)
        return f"{hours}时{minutes}分"

def drop_vlt2_table():
    """删除VLT2表（如果存在）"""
    print_newline("检查并删除已存在的VLT2表...")
    
    sqlCheckVlt2 = """
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE VLT2 PURGE';
    EXCEPTION
        WHEN OTHERS THEN
            IF SQLCODE != -942 THEN -- -942 是表不存在的错误代码
                RAISE;
            END IF;
    END;
    """
    
    with engine.connect() as conn:
        print_inline("删除VLT2表...")
        try:
            conn.execute(text(sqlCheckVlt2))
            conn.commit()
            print_newline("✓ VLT2表删除成功")
        except Exception as e:
            print_newline(f"删除VLT2表时出错: {e}")
            # 继续执行，可能表本来就不存在

def create_vlt2_table():
    """创建VLT2表"""
    print_newline("正在创建VLT2表...")
    
    sqlCreateVlt2 = """CREATE TABLE VLT2 (
        SETL_ID VARCHAR2(100),
        MDTRT_ID VARCHAR2(100),
        IN_OUT_ID VARCHAR2(100),
        PSN_NO VARCHAR2(100),
        CERTNO VARCHAR2(100),
        PSN_NAME VARCHAR2(100),
        AGE NUMBER,
        GEND VARCHAR2(10),
        BRDY DATE,
        HSP_CODE VARCHAR2(100),
        HSP_NAME VARCHAR2(600),
        HSP_ABBR VARCHAR2(30),
        J_ISIN CHAR(1),
        J_YEAR CHAR(4),
        IN_TIME DATE,
        OUT_TIME DATE,
        PERIOD NUMBER,
        GNR_C NUMBER,
        GNR_B NUMBER,
        IN_DEPT VARCHAR2(255),
        OUT_DEPT VARCHAR2(255),
        INSUTYPE VARCHAR2(50),
        DXMAINCODE VARCHAR2(4000),
        DXMAINNAME VARCHAR2(4000),
        DXCODEALL VARCHAR2(4000),
        DXNAMEALL VARCHAR2(4000),
        TXMAINCODE VARCHAR2(4000),
        TXMAINNAME VARCHAR2(4000),
        TXCODEALL VARCHAR2(4000),
        TXNAMEALL VARCHAR2(4000),
        ITEM_J_CODE VARCHAR2(4000),
        ITEM_J_NAME VARCHAR2(4000),
        ITEM_STD_CODE VARCHAR2(4000),
        ITEM_STD_NAME VARCHAR2(4000),
        ITEM_CODE VARCHAR2(4000),
        ITEM_NAME VARCHAR2(4000),
        ITEM_MED_CODE VARCHAR2(4000),
        ITEM_HSP_CODE VARCHAR2(4000),
        ITEM_HSP_NAME VARCHAR2(4000),
        ITEM_DEPT VARCHAR2(4000),
        APPLY_DEPT VARCHAR2(4000),
        ITEM_TIME VARCHAR2(4000),
        P VARCHAR2(4000),
        Q NUMBER,
        C NUMBER,
        B NUMBER,
        ITEM_J_CODE2 VARCHAR2(4000),
        ITEM_J_NAME2 VARCHAR2(4000),
        ITEM_CODE2 VARCHAR2(4000),
        ITEM_NAME2 VARCHAR2(4000),
        ITEM_MED_CODE2 VARCHAR2(4000),
        ITEM_HSP_CODE2 VARCHAR2(4000),
        ITEM_HSP_NAME2 VARCHAR2(4000),
        ITEM_DEPT2 VARCHAR2(4000),
        APPLY_DEPT2 VARCHAR2(4000),
        ITEM_TIME2 VARCHAR2(4000),
        P2 VARCHAR2(4000),
        Q2 NUMBER,
        C2 NUMBER,
        B2 NUMBER,
        VLT_Q NUMBER,
        VLT_P VARCHAR2(4000),
        VLT_C NUMBER,
        VLT_B NUMBER,
        RELATED VARCHAR2(4000),
        RULE_ACT_ID VARCHAR2(50),
        RULE_ID VARCHAR2(10),
        RULE_ACT_MSG VARCHAR2(4000),
        J_REFUND CHAR(1)
    )"""
    
    with engine.connect() as conn:
        print_inline("创建VLT2表...")
        conn.execute(text(sqlCreateVlt2))
        conn.commit()
        
    print_newline("✓ VLT2表创建成功")

def get_hsp_rule_combinations():
    """获取VLT表中所有的hsp_abbr和rule_id组合"""
    print_newline("获取VLT表中的hsp_abbr和rule_id组合...")
    
    sql_get_combinations = """
    SELECT DISTINCT hsp_abbr, rule_id, rule_act_id
    FROM vlt
    WHERE hsp_abbr IS NOT NULL AND rule_id IS NOT NULL AND rule_act_id IS NOT NULL
    ORDER BY hsp_abbr, rule_id
    """
    
    with engine.connect() as conn:
        result = conn.execute(text(sql_get_combinations))
        combinations = result.fetchall()
    
    return [(row[0], row[1], row[2]) for row in combinations]

def create_temp_tables():
    """创建辅助的临时表 dxs, txs, vlt2_t0"""
    # 检查是否存在临时表，存在则删除
    drop_temp_tables()

    print_newline("创建辅助的临时表 dxs, txs, vlt2_t0...")
    
    sql_create_dxs = """
    CREATE TABLE dxs AS
    SELECT
        setl_id,
        listagg(distinct case when dx.is_main=1 then dx.dx_code else null end,',') within group(order by dx.dx_code) as dxmaincode,
        listagg(distinct case when dx.is_main=1 then dx.dx_hsp_name else null end,',') within group(order by dx.dx_code) as dxmainname,
        listagg(distinct dx.dx_code,',') within group(order by dx.dx_code) as dxcodeall,
        listagg(distinct dx.dx_hsp_name,',') within group(order by dx.dx_hsp_name) as dxnameall
    FROM dx
    GROUP BY dx.setl_id
    """
    
    sql_create_txs = """
    CREATE TABLE txs AS
    SELECT
        setl_id,
        listagg(distinct case when tx.is_main=1 then tx.tx_code else null end,',') within group(order by tx.tx_code) as txmaincode,
        listagg(distinct case when tx.is_main=1 then tx.tx_hsp_name else null end,',') within group(order by tx.tx_code) as txmainname,
        listagg(distinct tx.tx_code,',') within group(order by tx.tx_code) as txcodeall,
        listagg(distinct tx.tx_hsp_name,',') within group(order by tx.tx_hsp_name) as txnameall
    FROM tx
    GROUP BY tx.setl_id
    """
    
    sql_create_vlt2_t0 = """
    CREATE TABLE vlt2_t0 AS
    SELECT
        vlt.setl_id, 
        vlt.item_j_code,
        listagg(distinct vlt.item_j_name,',') as item_j_name,
        max(d.item_std_code) as item_std_code,
        max(d.item_std_name) as item_std_name,
        listagg(distinct d.item_code,',') as item_code,
        listagg(distinct d.item_name,',') as item_name,
        listagg(distinct d.item_med_code,',') as item_med_code,
        listagg(distinct d.item_hsp_code,',') as item_hsp_code,
        listagg(distinct d.item_hsp_name,',') as item_hsp_name,
        listagg(distinct to_char(d.item_time,'yy-mm-dd hh24:mi:ss'),',') as item_time,
        vlt.item_day,
        listagg(distinct d.item_dept,',') as item_dept,
        -- listagg(distinct d.apply_dept,',') as apply_dept,
        CAST(NULL AS VARCHAR2(4000)) as apply_dept,
        listagg(distinct round(d.p,2),',') within group(order by d.p) as p,
        sum(vlt.q) as q,
        sum(vlt.c) as c,
        sum(vlt.b) as b,
        max(vlt.item_j_code2) as item_j_code2,
        max(vlt.item_j_name2) as item_j_name2,
        max(vlt.item_code2) as item_code2,
        max(vlt.item_name2) as item_name2,
        max(vlt.item_med_code2) as item_med_code2,
        max(vlt.item_hsp_code2) as item_hsp_code2,
        max(vlt.item_hsp_name2) as item_hsp_name2,
        max(vlt.item_time2) as item_time2,
        max(vlt.item_dept2) as item_dept2,
        -- max(vlt.apply_dept2) as apply_dept2,
        CAST(NULL AS VARCHAR2(4000)) as apply_dept2,
        max(vlt.p2) as p2,
        max(vlt.q2) as q2,
        max(vlt.c2) as c2,
        max(vlt.b2) as b2,
        max(vlt.related) as related,
        listagg(distinct round(vlt.vlt_p,2),',') within group(order by vlt.vlt_p) as vlt_p,
        sum(vlt.vlt_q) as vlt_q,
        sum(vlt.vlt_c) as vlt_c,
        sum(vlt.vlt_b) as vlt_b,
        vlt.rule_id, vlt.rule_act_id, max(vlt.rule_act_msg) as rule_act_msg
      FROM vlt, d
      WHERE vlt.d0_rowid = d.d0_rowid
      GROUP BY vlt.hsp_abbr, vlt.rule_id, vlt.rule_act_id, vlt.setl_id, vlt.item_day, vlt.item_j_code
      HAVING SUM(vlt.q) > 0
    """
    
    with engine.connect() as conn:
        conn.execute(text(sql_create_dxs))
        print_newline(" 创建dxs表完成")
        conn.execute(text(sql_create_txs))
        print_newline(" 创建txs表完成")
        conn.execute(text(sql_create_vlt2_t0))
        print_newline(" 创建vlt2_t0表完成")
        conn.commit()
    
    print_newline("✓ 辅助临时表创建成功")


def process_hsp_rule_combination(hsp_abbr, rule_id, rule_act_id, total_count, current_index, overall_start_time):
    """处理单个hsp_abbr, rule_id, rule_act_id组合的归纳"""

    # 显示进度
    elapsed_time = time.time() - overall_start_time
    progress_msg = f"[{current_index}/{total_count}] 处理 {hsp_abbr} - {rule_id} - {rule_act_id} | 用时:{format_time(elapsed_time)}"
    print_inline(progress_msg)
    
    # 构建SQL语句
    sql_process = f"""
    INSERT INTO VLT2
    SELECT
      t0.setl_id, m.mdtrt_id, m.in_out_id, m.psn_no, m.certno, m.psn_name, m.age, m.gend, m.brdy,
      m.hsp_code, m.hsp_name, m.hsp_abbr, m.j_isin, m.j_year, m.in_time, m.out_time, m.period,
      m.gnr_c, m.gnr_b, m.in_dept, m.out_dept, m.insutype,
      dxs.dxmaincode, dxs.dxmainname, dxs.dxcodeall, dxs.dxnameall,
      txs.txmaincode, txs.txmainname, txs.txcodeall, txs.txnameall,
      t0.item_j_code, t0.item_j_name, t0.item_std_code, t0.item_std_name,
      t0.item_code, t0.item_name, t0.item_med_code, t0.item_hsp_code, t0.item_hsp_name,
      t0.item_dept, t0.apply_dept, t0.item_time, t0.p, t0.q, t0.c, t0.b,
      t0.item_j_code2, t0.item_j_name2, t0.item_code2, t0.item_name2, t0.item_med_code2, t0.item_hsp_code2, t0.item_hsp_name2,
      t0.item_dept2, t0.apply_dept2, t0.item_time2, t0.p2, t0.q2, t0.c2, t0.b2,
      t0.vlt_q, t0.vlt_p, t0.vlt_c, t0.vlt_b, t0.related, t0.rule_act_id, t0.rule_id, t0.rule_act_msg,
      CAST(NULL AS CHAR(1)) as j_refund
    FROM vlt2_t0 t0, m 
      LEFT OUTER JOIN dxs ON m.setl_id = dxs.setl_id
      LEFT OUTER JOIN txs ON m.setl_id = txs.setl_id
    WHERE t0.setl_id = m.setl_id and m.hsp_abbr = '{hsp_abbr}' AND t0.rule_id = '{rule_id}' AND t0.rule_act_id = '{rule_act_id}'
    ORDER BY m.psn_no, t0.setl_id, t0.item_day
    """
    
    try:
        with engine.connect() as conn:
            result = conn.execute(text(sql_process))
            affected_rows = result.rowcount
            conn.commit()
            return affected_rows
    except Exception as e:
        print_newline(f"处理 {hsp_abbr} - {rule_id} - {rule_act_id} 时发生错误: {e}")
        return 0

def drop_temp_tables():
    """删除辅助的临时表 dxs, txs, vlt2_t0"""
    print_newline("正在删除辅助的临时表 dxs, txs, vlt2_t0...")
    
    sql_drop_tables = [
        "DROP TABLE dxs PURGE",
        "DROP TABLE txs PURGE",
        "DROP TABLE vlt2_t0 PURGE"
    ]
    
    with engine.connect() as conn:
        for sql in sql_drop_tables:
            try:
                print_inline(f"删除表: {sql.split()[2]}...")
                conn.execute(text(sql))
                conn.commit()
            except Exception as e:
                print_newline(f"删除表时出错: {e}")
    
    print_newline("✓ 辅助临时表删除完成")

def create_vlt2_indexes():
    """为VLT2表创建索引"""
    print_newline("正在为VLT2表创建索引...")
    
    indexes = [
        "CREATE INDEX idx_vlt2_setl_id ON VLT2 (SETL_ID)",
        "CREATE INDEX idx_vlt2_psn_no ON VLT2 (PSN_NO)",
        "CREATE INDEX idx_vlt2_hsp_abbr ON VLT2 (HSP_ABBR)",
        "CREATE INDEX idx_vlt2_rule_id ON VLT2 (RULE_ID)",
        "CREATE INDEX idx_vlt2_rule_act_id ON VLT2 (RULE_ACT_ID)",
        "CREATE INDEX idx_vlt2_j_year ON VLT2 (J_YEAR)",
        "CREATE INDEX idx_vlt2_j_isin ON VLT2 (J_ISIN)"
    ]
    
    with engine.connect() as conn:
        for idx, index_sql in enumerate(indexes):
            try:
                index_name = index_sql.split('CREATE INDEX ')[1].split(' ON')[0]
                print_inline(f"创建索引 [{idx+1}/{len(indexes)}]: {index_name}")
                conn.execute(text(index_sql))
            except Exception as e:
                print_newline(f"创建索引失败: {index_sql}, 错误: {e}")
        
        conn.commit()
    
    print_newline("✓ VLT2表索引创建完成")

def main():
    """主函数"""
    print_newline("=" * 80)
    print_newline("飞检数据STEP8筛选 - conclude模块")
    print_newline("将VLT表信息归纳到VLT2表")
    print_newline("=" * 80)
    
    overall_start_time = time.time()
    
    try:
        # 步骤1: 删除已存在的VLT2表
        print_newline("步骤1: 删除已存在的VLT2表")
        drop_vlt2_table()
        
        # 步骤2: 创建VLT2表
        print_newline("步骤2: 创建VLT2表")
        create_vlt2_table()
        
        # 步骤3: 获取所有hsp_abbr和rule_id组合
        print_newline("步骤3: 获取hsp_abbr和rule_id组合")
        combinations = get_hsp_rule_combinations()
        total_count = len(combinations)
        print_newline(f"找到 {total_count} 个hsp_abbr和rule_id组合")
        
        # 步骤4: 逐一处理每个组合
        print_newline("步骤4: 创建辅助临时表")
        create_temp_tables()

        print_newline("步骤4: 处理数据归纳")
        total_rows_processed = 0
        
        for i, (hsp_abbr, rule_id, rule_act_id) in enumerate(combinations, 1):
            affected_rows = process_hsp_rule_combination(hsp_abbr, rule_id, rule_act_id, total_count, i, overall_start_time)
            total_rows_processed += affected_rows
        
        print_newline(f"✓ 数据归纳完成，共处理 {total_rows_processed} 行数据")

        drop_temp_tables()
        
        # 步骤5: 创建VLT2表索引
        print_newline("步骤5: 创建VLT2表索引")
        create_vlt2_indexes()
        
        # 完成
        total_time = time.time() - overall_start_time
        print_newline("")
        print_newline("=" * 80)
        print_newline("✓ VLT数据归纳任务完成！")
        print_newline(f"总用时: {format_time(total_time)}")
        print_newline(f"处理组合数: {total_count}")
        print_newline(f"归纳行数: {total_rows_processed}")
        print_newline("=" * 80)
        
    except Exception as e:
        print_newline(f"处理过程中发生错误: {e}")
        raise

if __name__ == "__main__":
    main()
